﻿CREATE PROCEDURE [maint].[DefragDb]
@DbName [sysname], @CheckSchedule BIT=1, @OutputDiagnostics BIT=0
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @RC INT, @ERROR INT;
	SELECT @RC = 0, @ERROR = 0;

	IF(@OutputDiagnostics = 1)
		BEGIN
			PRINT OBJECT_NAME(@@PROCID)
			PRINT N'	@DbName = N''' + ISNULL(@DbName,N'NULL') + '''';
			PRINT N'	@CheckSchedule = N''' + ISNULL(CAST(@CheckSchedule AS nvarchar),N'NULL') + '''';
		END

	IF(@DbName IN (N'master',N'model',N'tempdb'))
	OR(DATABASEPROPERTY(@DbName,'IsReadOnly') = 1)
	OR(DATABASEPROPERTYEX(@DbName,'Status') <> 'ONLINE')
		GOTO EXIT_PROC

	DECLARE @ActionToken CHAR(1);
	SET @ActionToken = 'I';
	
	DECLARE @DefragRequired bit
	SET @DefragRequired = 1;
	
	IF (@CheckSchedule = 1)
		EXEC @RC = maint.GetDefragScheduleStatus @DbName, NULL, @DefragRequired OUTPUT, @OutputDiagnostics;
		
	IF (@DefragRequired = 0)
	BEGIN
		--check previous status
		EXEC @RC = maint.CheckErrorStatus @DbName, @ActionToken;
		
		GOTO EXIT_PROC
	END
		
	PRINT REPLICATE('-',100);
	PRINT N'REBUILDING FRAGMENTED INDEXES FOR DATABASE ' + UPPER(@DbName);

	DECLARE @SelectQuery nvarchar(1024)
	SET @SelectQuery = N'
	DECLARE TABLE_CUR CURSOR GLOBAL FAST_FORWARD FOR(
		SELECT N''['' + [TABLE_SCHEMA] + N''].['' + [TABLE_NAME] + N'']''
		FROM [' + @DbName + '].INFORMATION_SCHEMA.TABLES
		INNER JOIN [' + @DbName + '].dbo.sysobjects AS so
			ON (so.[name] = [TABLE_NAME])
		INNER JOIN [' + @DbName + '].dbo.sysindexes AS si
			ON (si.[ID] = so.[ID])
			AND(si.[indid] > 0)
		WHERE ([TABLE_TYPE] = ''BASE TABLE'')
		GROUP BY
			[TABLE_SCHEMA], [TABLE_NAME]
	)'
	EXEC @RC = sp_executesql @SelectQuery
	IF(@RC <> 0) GOTO EXIT_PROC

	OPEN TABLE_CUR

	DECLARE @TableName sysname
	FETCH NEXT FROM TABLE_CUR INTO @TableName
	WHILE(@@FETCH_STATUS = 0)
		BEGIN
			EXEC @RC = maint.DefragTable @DbName, @TableName, @OutputDiagnostics;
			IF(@RC <> 0) SET @ERROR = @RC;

			FETCH NEXT FROM TABLE_CUR INTO @TableName
		END
	
	IF (CURSOR_STATUS('global','TABLE_CUR') >= 0)
		BEGIN
			CLOSE TABLE_CUR
			DEALLOCATE TABLE_CUR
		END
		
	IF(@ERROR = 0)
		BEGIN
			--clear error status flag
			EXEC maint.SetErrorStatus @DbName, @ActionToken, 0;		
		END
	ELSE
		BEGIN
			--bubble up last error
			SET @RC = @ERROR;

			--set error status flag	
			EXEC maint.SetErrorStatus @DbName, @ActionToken, 1;
			
			RAISERROR('Index defragmentation for database %s failed. Check previous error messages for details.',16,1,@DbName);
		END
	
EXIT_PROC:
	RETURN(@RC)
END